﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using System.Text;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;

namespace Sinacor.Servico.Bovespa.Cliente.ResourceAccess
{
    /// <summary>
    /// ResourceAccess Class - DicionarioErroExportacaoComitente
    /// </summary>
    public class DicionarioErroExportacaoComitente : BaseResourceAccess
    {
        #region Construtor

        /// <summary>
        /// Construtor da classe DicionarioErroExportacaoComitente
        /// </summary>
        public DicionarioErroExportacaoComitente()
            : base(AuthorizationContext.Current.CompanyId)
        {
        }

        #endregion

        #region [PUB] AlterarErroExportacaoComitente

        /// <summary>
        /// Altera um erro na Exportação do Comitente
        /// </summary>
        /// <param name="request">Entidade ErroExportacaoComitente</param>
        public void AlterarErroExportacaoComitente(BusinessEntity.ErroExportacaoComitente request)
        {
            #region SQL

            StringBuilder sql = new StringBuilder();

            sql.Append("UPDATE TBODESCERRO ");
            sql.Append("SET NM_ERRO = :Descricao ");
            sql.Append("WHERE ");
            sql.Append("CD_ERRO = :Codigo ");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString());

            #endregion

            #region Parametros

            this.DataBase.AddInParameter(dbCommand, "Descricao", DbType.String, request.Descricao);
            this.DataBase.AddInParameter(dbCommand, "Codigo", DbType.Int32, request.Codigo);

            #endregion

            #region Execucao

            this.DataBase.ExecuteNonQuery(dbCommand);

            #endregion
        }

        #endregion

        #region [PUB] ConsultarErrosExportacoesComitentes

        /// <summary>
        /// Consulta erros na Exportação do Comitente 
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<BusinessEntity.ErroExportacaoComitente> ConsultarErrosExportacoesComitentes(BusinessEntity.Parameter.FiltroErroExportacaoComitente request)
        {
            List<BusinessEntity.ErroExportacaoComitente> response = new List<Sinacor.Servico.Bovespa.Cliente.BusinessEntity.ErroExportacaoComitente>();
            
            StringBuilder sql = new StringBuilder();
            
            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(" "))
            {

            #region SQL

            sql.Append("SELECT ");
            sql.Append("CD_ERRO AS Codigo, ");
            sql.Append("NM_ERRO AS Descricao ");
            sql.Append("FROM TBODESCERRO ");
            sql.Append("WHERE 1 = 1 ");
                
            //Filtro Opcional - Código do Erro
            if (request.Codigo.HasValue)
            {
                sql.Append("AND CD_ERRO = :Codigo ");
                this.DataBase.AddInParameter(dbCommand, "Codigo", DbType.Int32, request.Codigo);
            }
            if (!string.IsNullOrEmpty(request.Descricao))
            {
                sql.Append("AND UPPER(NM_ERRO) LIKE '%'||:Descricao||'%' ");
                this.DataBase.AddInParameter(dbCommand, "Descricao", DbType.String, request.Descricao.ToUpper());
            }
            sql.Append("ORDER BY CD_ERRO");

            dbCommand.CommandText = sql.ToString(); 

            #endregion

            #region Execucao

                response = this.FindAll<BusinessEntity.ErroExportacaoComitente>(dbCommand,
                    reader =>
                    {
                        BusinessEntity.ErroExportacaoComitente entity = new BusinessEntity.ErroExportacaoComitente();

                        entity.Codigo = reader.GetValue<Int32?>("Codigo");
                        entity.Descricao = reader["Descricao"].ToString();

                        return entity;
                    });

                return response;

            }

            #endregion
        }

        #endregion

        #region [PUB] ExcluirErroExportacaoComitente

        /// <summary>
        /// Exclui Erros na Exportação do Comitente
        /// </summary>
        /// <param name="request"></param>
        public void ExcluirErroExportacaoComitente(BusinessEntity.Parameter.FiltroErroExportacaoComitenteChave request)
        {
            #region SQL

            StringBuilder sql = new StringBuilder();

            sql.Append("DELETE FROM TBODESCERRO ");
            sql.Append("WHERE CD_ERRO = :Codigo");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString());

            #endregion

            #region Parametros

            this.DataBase.AddInParameter(dbCommand, "Codigo", DbType.Int32, request.Codigo);

            #endregion

            #region Execucao

            this.DataBase.ExecuteNonQuery(dbCommand);

            #endregion
        }

        #endregion

        #region [PUB] IncluirErroExportacaoComitente

        /// <summary>
        /// Inclui Erro na Exportação do Comitente
        /// </summary>
        /// <param name="request"></param>
        public void IncluirErroExportacaoComitente(BusinessEntity.ErroExportacaoComitente request)
        {
            #region SQL

            StringBuilder sql = new StringBuilder();

            sql.Append("INSERT INTO TBODESCERRO ");
            sql.Append("( CD_ERRO,NM_ERRO ) ");
            sql.Append("VALUES ");
            sql.Append("( :Codigo,:Descricao )");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString());

            #endregion

            #region Parametros

            this.DataBase.AddInParameter(dbCommand, "Codigo", DbType.Int32, request.Codigo);
            this.DataBase.AddInParameter(dbCommand, "Descricao", DbType.String, request.Descricao);

            #endregion

            #region Execucao

            this.DataBase.ExecuteNonQuery(dbCommand);

            #endregion
        }

            #endregion

        #region [PUB] ObterErroExportacaoComitente

        /// <summary>
        /// Obtém um Erro na Exportação do Comitente
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public BusinessEntity.ErroExportacaoComitente ObterErroExportacaoComitente(BusinessEntity.Parameter.FiltroErroExportacaoComitenteChave request)
        {
            BusinessEntity.ErroExportacaoComitente response;

            #region SQL

            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT ");
            sql.Append("CD_ERRO AS Codigo, ");
            sql.Append("NM_ERRO AS Descricao ");
            sql.Append("FROM TBODESCERRO ");
            sql.Append("WHERE CD_ERRO = :Codigo");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {

            #endregion

            #region Parametros

            this.DataBase.AddInParameter(dbCommand, "Codigo", DbType.Int32, request.Codigo);

            #endregion

            #region Execucao
                                
                response = this.FindOne<BusinessEntity.ErroExportacaoComitente>(dbCommand,
                    reader =>
                        {
                            response = new Sinacor.Servico.Bovespa.Cliente.BusinessEntity.ErroExportacaoComitente();

                            response.Codigo = reader.GetValue<Int32>("Codigo");
                            response.Descricao = reader.GetValue<String>("Descricao");
                            return response;
                        });
            }

            #endregion

            return response;
        }
            #endregion
    }
}
